package uk.ac.imperial.gpaexpress.server;

import java.io.StringWriter;
import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Hashtable;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;

@SuppressWarnings("serial")
public class Query extends HttpServlet {
	String db_url = "jdbc:postgresql:richard";
	String db_user = "richard";
	String db_password = "rhymenoceros";
	
	Connection conn;
	String user;
	String model_title;
	int model_id;
	String params_title;
	int params_id;
	String type;

	public void doPost(HttpServletRequest req, HttpServletResponse resp){
		// Set response type
		resp.setContentType("text/html");

		// Get session
		HttpSession session = req.getSession(true);
		user = (String) session.getAttribute("email");

		// Get type of resource
		type = req.getHeader("type");

		// Get db connection
		try {
			conn = DriverManager.getConnection(
					db_url,db_user,db_password);
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		
		if(type.equals("share")){	// request is to share model with another user
			try{
				// Get id of model being shared and message for recipient
				int model_id = Integer.valueOf(req.getHeader("model").split("#")[1]);
				String shareWith = req.getHeader("shareWith");
				String message = req.getHeader("message");
				
				// Copy model record for new owner with old_model_id, get new_model_id
				Statement modelStmt = conn.createStatement();
				ResultSet rs = modelStmt.executeQuery("SELECT title,source,model_xml FROM models WHERE id = " + model_id);
				if(rs.next()){
					model_title = rs.getString("title");
					String owner = shareWith;
					String source = rs.getString("source");
					String model_xml = rs.getString("model_xml");
					PreparedStatement updateModels = conn.prepareStatement("INSERT INTO models(title,owner,source,model_xml) VALUES(?,?,?,?)", 
							Statement.RETURN_GENERATED_KEYS);
					updateModels.setString(1, model_title);
					updateModels.setString(2, owner);
					updateModels.setString(3, source);
					updateModels.setString(4, model_xml);
					updateModels.execute();
					ResultSet modelKeySet = updateModels.getGeneratedKeys();
					modelKeySet.next();
					int new_model_id = modelKeySet.getInt("id");
					
					modelStmt.close();
					updateModels.close();
					
					
					// Copy each params record with old_model_id, replacing owner
					// Build array of old_params_id, new_params_id pairs for use in plots relation
					Statement paramsStmt = conn.createStatement();
					ResultSet rs2 = paramsStmt.executeQuery("SELECT id, title, model_id, change_constants FROM params WHERE model_id = " + model_id);
					PreparedStatement updateParams = conn.prepareStatement("INSERT INTO params(title,model_id,change_constants) VALUES(?,?,?)",
							Statement.RETURN_GENERATED_KEYS);
					Hashtable<Integer, Integer> paramIds = new Hashtable<Integer, Integer>();
					while(rs2.next()){
						int old_params_id = rs2.getInt("id");
						String params_title = rs2.getString("title");
						String change_constants = rs2.getString("change_constants");
						
						updateParams.setString(1, params_title);
						updateParams.setInt(2, new_model_id);
						updateParams.setString(3, change_constants);
						updateParams.execute();
						ResultSet paramKeySet = updateParams.getGeneratedKeys();
						paramKeySet.next();
						int new_params_id = paramKeySet.getInt("id");
						
						paramIds.put(old_params_id, new_params_id);
					}
					paramsStmt.close();
					updateParams.close();
					
					
					// Copy each analysis record with old_model_id, replacing model_id
					// Build array of old_analysis_id, new_analysis_id pairs for use in iterates and plots relations
					Statement analysisStmt = conn.createStatement();
					ResultSet rs3 = analysisStmt.executeQuery("SELECT id, type, stepSize, stopTime FROM analyses WHERE model_id = " + model_id);
					PreparedStatement updateAnalyses = conn.prepareStatement("INSERT INTO analyses(type,model_id,stepSize,stopTime) VALUES(?,?,?,?)",
							Statement.RETURN_GENERATED_KEYS);
					Hashtable<Integer, Integer> analysisIds = new Hashtable<Integer, Integer>();
					while(rs3.next()){
						int old_analysis_id = rs3.getInt("id");
						String type = rs3.getString("type");
						double stepSize = rs3.getDouble("stepSize");
						double stopTime = rs3.getDouble("stopTime");
						
						updateAnalyses.setString(1, type);
						updateAnalyses.setInt(2, new_model_id);
						updateAnalyses.setDouble(3, stepSize);
						updateAnalyses.setDouble(4, stopTime);
						updateAnalyses.execute();
						ResultSet analysisKeySet = updateAnalyses.getGeneratedKeys();
						analysisKeySet.next();
						int new_analysis_id = analysisKeySet.getInt("id");
						analysisIds.put(old_analysis_id, new_analysis_id);
					}
					analysisStmt.close();
					updateAnalyses.close();
					
					
					// Copy each iterates record with old_analysis_id, replacing with new_analysis_id
					PreparedStatement iterateStmt = conn.prepareStatement("SELECT analysis_id, constant, fromVal, toVal, steps FROM iterates WHERE analysis_id = ANY(?)");
					Array oldAnalysisIds = conn.createArrayOf("integer", analysisIds.keySet().toArray());
					iterateStmt.setArray(1, oldAnalysisIds);
					ResultSet rs4 = iterateStmt.executeQuery();
					PreparedStatement updateIterates = conn.prepareStatement("INSERT INTO iterates(analysis_id,constant,fromVal,toVal,steps) " +
							"VALUES(?,?,?,?,?)");
					while(rs4.next()){
						int old_analysis_id = rs4.getInt("analysis_id");
						String constant = rs4.getString("constant");
						double fromVal = rs4.getDouble("fromVal");
						double toVal = rs4.getDouble("toVal");
						int steps = rs4.getInt("steps");
						int new_analysis_id = analysisIds.get(old_analysis_id);
						updateIterates.setInt(1, new_analysis_id);
						updateIterates.setString(2, constant);
						updateIterates.setDouble(3, fromVal);
						updateIterates.setDouble(4, toVal);
						updateIterates.setInt(5, steps);
						updateIterates.execute();
					}
					iterateStmt.close();
					updateIterates.close();
					
					// Copy each plots with old_params_id, replacing params and analysis id's with new equivalents
					PreparedStatement plotsStmt = conn.prepareStatement("SELECT params_id,analysis_id,axes,labels,x_data,y_data,z_data " +
							"FROM plots WHERE params_id = ANY(?)");
					Array oldParamIds = conn.createArrayOf("integer", paramIds.keySet().toArray());
					plotsStmt.setArray(1, oldParamIds);
					ResultSet rs5 = plotsStmt.executeQuery();
					PreparedStatement updatePlots = conn.prepareStatement("INSERT INTO plots(params_id,analysis_id,axes,labels,x_data,y_data,z_data) "
							+ "VALUES(?,?,?,?,?,?,?)");
					while(rs5.next()){
						int newParamId = paramIds.get(rs5.getInt("params_id"));
						int newAnalysisId = analysisIds.get(rs5.getInt("analysis_id"));
						Array axes = rs5.getArray("axes");
						Array labels = rs5.getArray("labels");
						String x_data = rs5.getString("x_data");
						String y_data = rs5.getString("y_data");
						String z_data = rs5.getString("z_data");
						
						updatePlots.setInt(1,newParamId);
						updatePlots.setInt(2,newAnalysisId);
						updatePlots.setArray(3,axes);
						updatePlots.setArray(4,labels);
						updatePlots.setString(5,x_data);
						updatePlots.setString(6,y_data);
						updatePlots.setString(7,z_data);
						updatePlots.execute();
					}
					plotsStmt.close();
					updatePlots.close();
					
					// Leave notification for recipient
					Calendar currentDate = Calendar.getInstance();
					SimpleDateFormat formatter =  new SimpleDateFormat("MMM dd HH:mm:ss");
					String dateTime = formatter.format(currentDate.getTime());
					String shareMessage = user.substring(0, user.indexOf('@')) + " shared " + model_title + " with you.";
					PreparedStatement updateNotifications = conn.prepareStatement("INSERT INTO notifications(recipient,timestamp,message) "
							+ "VALUES(?,?,?)");
					updateNotifications.setString(1,shareWith);
					updateNotifications.setString(2,dateTime);
					updateNotifications.setString(3, shareMessage + "\n-\"" + message + "\"");
					updateNotifications.execute();
					
				}
			}catch(Exception e){
				e.printStackTrace();
			}
			
			
			
		}else if(type.equals("copy")){ // request is to move all results for old model to new model, deleting old model
			try{
				int old_id = Integer.valueOf(req.getHeader("old_id"));
				int new_id = Integer.valueOf(req.getHeader("new_id"));

				// Delete default params for new model
				Statement delNewParams = conn.createStatement();
				delNewParams.execute("DELETE from params WHERE model_id = " + new_id);
				delNewParams.close();
				
				// Move param sets to new model
				Statement getOldParams = conn.createStatement();
				getOldParams.execute("UPDATE params SET model_id = " + new_id + " WHERE model_id = " + old_id);
				getOldParams.close();
				
				//Delete all new analyses except added one
				Statement findNewAnalyses = conn.createStatement();
				ResultSet newAnalysesSet = findNewAnalyses.executeQuery("SELECT id FROM analyses WHERE model_id = " + new_id);
				Statement delNewAnalysis = conn.createStatement();
				while(newAnalysesSet.next()){
					if(!newAnalysesSet.isLast()){
						delNewAnalysis.execute("DELETE FROM analyses WHERE id = " + newAnalysesSet.getInt("id"));
					}
				}
				delNewAnalysis.close();
				findNewAnalyses.close();
				
				// Move old analyses to new model
				Statement getOldAnalyses = conn.createStatement();
				getOldAnalyses.execute("UPDATE analyses SET model_id = " + new_id + " WHERE model_id = " + old_id);
				getOldAnalyses.close();
				
				// Delete old model
				Statement delOldModel = conn.createStatement();
				delOldModel.execute("DELETE FROM models WHERE id = " + old_id);
				delOldModel.close();
				conn.close();
			}catch(Exception e){
				e.printStackTrace();
			}
		}else if(type.equals("delete")){
			try{
				// Delete params_id record (all analyses and results will delete on cascade
				params_id = Integer.valueOf(req.getHeader("params").split("#")[1]);
				Statement deleteParamStmt = conn.createStatement();
				deleteParamStmt.execute("DELETE FROM params WHERE id = " + params_id);
				deleteParamStmt.close();
				
				// Check if no paramsets left for this model then delete model
				model_id = Integer.valueOf(req.getHeader("model").split("#")[1]);
				Statement countStmt = conn.createStatement();
				ResultSet countRs = countStmt.executeQuery("SELECT id FROM params WHERE model_id = " + model_id);
				if(!countRs.next()){
					// No param sets left so delete model
					Statement deleteModelStmt = conn.createStatement();
					deleteModelStmt.execute("DELETE FROM models WHERE id = " + model_id);
					deleteModelStmt.close();
					resp.getWriter().write("model_deleted");
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}else if(type.equals("paramSet")){
			try {
				String title = req.getHeader("paramsTitle");
				model_title = req.getHeader("model").split("#")[0].trim();
				model_id = Integer.valueOf(req.getHeader("model").split("#")[1]);
				String paramStr = req.getHeader("paramStr");
				PreparedStatement updateParams = conn.prepareStatement("INSERT INTO params(title,model_id,change_constants) "
						+ "VALUES(?,?,?)",Statement.RETURN_GENERATED_KEYS);
				updateParams.setString(1,title);
				updateParams.setInt(2,model_id);
				updateParams.setString(3,paramStr);
				updateParams.executeUpdate();
				ResultSet paramsKeys = updateParams.getGeneratedKeys();
				while(paramsKeys.next()){
					params_id = paramsKeys.getInt("id");
				}
				resp.getWriter().write(String.valueOf(params_id));
			} catch (Exception e){
				e.printStackTrace();
			}
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void doGet(HttpServletRequest req, HttpServletResponse resp){
		// Set response type
		resp.setContentType("text/html");

		// Get session
		HttpSession session = req.getSession(true);
		user = (String) session.getAttribute("email");

		// Get type of request
		type = req.getHeader("type");

		// Get db connection
		try {
			conn = DriverManager.getConnection ("jdbc:postgresql:richard","richard", "rhymenoceros" );
		} catch (SQLException e1) {
			e1.printStackTrace();
		}

		if(type.equals("notifications")){			// request was for notifications
			try{
				// Build xml document to return notifications
				DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
				DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
				Document document = docBuilder.newDocument();
				
				// Get notifications and add to document
				Statement notifyStmt = conn.createStatement();
				Statement delNotify = conn.createStatement();
				ResultSet notifications = notifyStmt.executeQuery("SELECT id, timestamp, message FROM notifications WHERE recipient = '" + user + "'");
				while(notifications.next()){
					int id = notifications.getInt("id");
					String timestamp = notifications.getString("timestamp");
					String message = notifications.getString("message");
					Element notificationEm = document.createElement("notification");
					notificationEm.setAttribute("timestamp", timestamp);
					notificationEm.setAttribute("message", message);
					document.appendChild(notificationEm);
					
					// remove notification from db to acknowledge receipt
					delNotify.execute("DELETE FROM notifications WHERE id = " + id);
					
				}
				notifyStmt.close();
				delNotify.close();
				
				// Return xmlString
				TransformerFactory transfac = TransformerFactory.newInstance();
				Transformer trans = transfac.newTransformer();
				trans.setOutputProperty(OutputKeys.INDENT, "yes");
				StringWriter sw = new StringWriter();
				StreamResult result = new StreamResult(sw);
				DOMSource source = new DOMSource(document);
				trans.transform(source, result);
				String notificationsXml = sw.toString();
				resp.getWriter().write(notificationsXml);
				
			}catch(Exception e){
				e.printStackTrace();
			}
			
		}else if(type.equals("model_xml")){			// request was for model_xml
			model_title = req.getHeader("model").split("#")[0].trim();
			model_id = Integer.valueOf(req.getHeader("model").split("#")[1]);
			// Fetch model_xml for model + params from db
			try {
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT model_xml FROM models "
						+ "WHERE id = " + model_id);

				//if resource exists
				if(rs.next()){
					resp.getWriter().write(rs.getString("model_xml"));
				}
			} catch (Exception e) {e.printStackTrace();}


		}else if(type.equals("params")){		// request was for param list, get modelname and return xml list of params
			model_title = req.getHeader("model").split("#")[0].trim();
			model_id = Integer.valueOf(req.getHeader("model").split("#")[1]);

			try {
				// Create xml doc
				DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
				DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
				Document document = docBuilder.newDocument();
				// Get resultset of param titles from db
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT params.id, params.title FROM params WHERE model_id = " + model_id);

				// For each paramSet title create element and add to xml doc
				Element rootElement = document.createElement("ParameterList");
				document.appendChild(rootElement);

				while(rs.next()){

					int params_id = rs.getInt("id");
					String title = rs.getString("title");
					Element paramElement = document.createElement("params");
					paramElement.setAttribute("title", title);
					paramElement.setAttribute("id", String.valueOf(params_id));
					rootElement.appendChild(paramElement);

				}

				// Return xmlString
				TransformerFactory transfac = TransformerFactory.newInstance();
				Transformer trans = transfac.newTransformer();
				trans.setOutputProperty(OutputKeys.INDENT, "yes");
				StringWriter sw = new StringWriter();
				StreamResult result = new StreamResult(sw);
				DOMSource source = new DOMSource(document);
				trans.transform(source, result);
				String paramsXml = sw.toString();
				resp.getWriter().write(paramsXml);
			} catch (Exception e) {e.printStackTrace();}

		}else if(type.equals("constStr")){						// Request was for new constants of paramSet

			params_title = req.getHeader("params").split("#")[0].trim();
			params_id = Integer.valueOf(req.getHeader("params").split("#")[1]);
			try {
				Statement stmt = conn.createStatement();

				ResultSet constSet = stmt.executeQuery("SELECT change_constants FROM params WHERE id=" + params_id);
				if(constSet.next()){
					resp.getWriter().write(constSet.getString("change_constants"));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

		}else if(type.equals("results")){								// Request was for results
			model_title = req.getHeader("model").split("#")[0].trim();
			model_id = Integer.valueOf(req.getHeader("model").split("#")[1]);
			params_title = req.getHeader("params").split("#")[0].trim();
			params_id = Integer.valueOf(req.getHeader("params").split("#")[1]);
			try{
				// Create xml doc representation of plots
				DocumentBuilderFactory dbfac = DocumentBuilderFactory.newInstance();
				DocumentBuilder docBuilder = dbfac.newDocumentBuilder();
				Document document = docBuilder.newDocument();
				// Get results from db
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT analyses.type, analyses.stepSize, analyses.stopTime, "
						+ "plots.axes, plots.labels, plots.x_data, plots.y_data, plots.z_data "
						+ "FROM analyses,plots "
						+ "WHERE analyses.model_id = " + model_id + " "
						+ "AND plots.params_id= " + params_id + " "
						+ "AND plots.analysis_id = analyses.id "
				);
				Element rootEm = document.createElement("results");
				document.appendChild(rootEm);
				while(rs.next()){
					// Get plot info
					String type = rs.getString("type");
					Double stepSize = rs.getDouble("stepSize");
					Double stopTime = rs.getDouble("stopTime");
					String[] axes = (String[])rs.getArray("axes").getArray();
					String[] labels = (String[])rs.getArray("labels").getArray();
					// Create element for plot
					Element plotEm = document.createElement("plot");
					plotEm.setAttribute("stepSize", String.valueOf(stepSize));
					plotEm.setAttribute("stopTime", String.valueOf(stopTime));
					rootEm.appendChild(plotEm);
					// Get data and axes for plot
					String x_data = rs.getString("x_data");
					String y_data = rs.getString("y_data");
					String z_data = rs.getString("z_data");

					// Result is optimal value
					if(axes[0].equals("value")){
						plotEm.setAttribute("type","optimal_value");
						Element valEm = document.createElement("x_data");
						valEm.setAttribute("name", labels[0]);
						valEm.setAttribute("optimum", x_data);
						plotEm.appendChild(valEm);
					}
					// Result is standard time-count
					else if(axes[0].equals("time") && axes[1].equals("count")){
						plotEm.setAttribute("type", type);
						String[] yArr = y_data.split(";");
						for(int i=0; i<labels.length; i++){
							Element yEm = document.createElement("y_data");
							yEm.setAttribute("label", labels[i]);
							yEm.setTextContent(yArr[i]);
							plotEm.appendChild(yEm);
						}
					}
					// Result is 1d iteration
					else if(axes[1].equals("count")){
						plotEm.setAttribute("type", "1d Iterate Experiment");
						Element xEm = document.createElement("x_data");
						xEm.setAttribute("axis", axes[0]);
						xEm.setTextContent(x_data);
						plotEm.appendChild(xEm);
						Element yEm = document.createElement("y_data");
						yEm.setTextContent(y_data);
						yEm.setAttribute("label", labels[0]);
						plotEm.appendChild(yEm);
					}
					// result is 2d iteration
					else if(axes.length == 3){
						plotEm.setAttribute("type", "2d Iterate Experiment");
						Element xEm = document.createElement("x_data");
						xEm.setAttribute("axis", axes[0]);
						xEm.setTextContent(x_data);
						plotEm.appendChild(xEm);
						Element yEm = document.createElement("y_data");
						yEm.setAttribute("axis", axes[1]);
						yEm.setTextContent(y_data);
						plotEm.appendChild(yEm);
						Element zEm = document.createElement("z_data");
						zEm.setAttribute("label", labels[0]);
						zEm.setTextContent(z_data);
						plotEm.appendChild(zEm);
					}

				}

				// Return xmlString
				TransformerFactory transfac = TransformerFactory.newInstance();
				Transformer trans = transfac.newTransformer();
				trans.setOutputProperty(OutputKeys.INDENT, "yes");
				StringWriter sw = new StringWriter();
				StreamResult result = new StreamResult(sw);
				DOMSource source = new DOMSource(document);
				trans.transform(source, result);
				String resultsXml = sw.toString();
				resp.getWriter().write(resultsXml);
			}catch(Exception e){
				e.printStackTrace();
			}
		}
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
